package org.seven.jrdp.web.t;

import java.sql.Timestamp;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;
import org.seven.jrdp.biz.t.model.TYs;
import org.seven.jrdp.biz.t.service.TCrkService;
import org.seven.jrdp.commons.mybatis.model.PageRequest;
import org.seven.jrdp.commons.util.Excel2007Utils;
import org.seven.jrdp.commons.util.ServletUtils;
import org.seven.jrdp.web.t.plugins.ExcelPlugin;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.servlet.ModelAndView;

import com.github.pagehelper.PageInfo;

@RestController
@RequestMapping("/mgt/t/crk")
public class TCrkController {
	private static final String EXCEL_CRK = "crk.xlsx";
	private static final int LEN_CKJL = 3;
	@Autowired
	private TCrkService tCrkService;

	@GetMapping("/{view}")
	public ModelAndView view(@PathVariable String view) {
		return new ModelAndView("mgt/t/crk/crk_" + view);
	}

	@PostMapping("/find")
	public PageInfo<Map<String, Object>> find(PageRequest pageRequest, HttpServletRequest request) {
		Map<String, Object> map = ServletUtils.getSearchParams(request);
		PageInfo<Map<String, Object>> page = tCrkService.find(map, pageRequest);
		return page;
	}

	@RequestMapping("/dc")
	public void dc(HttpServletRequest request, HttpServletResponse response) {
		Map<String, Object> map = ServletUtils.getSearchParams(request);
		Map<String, Object> dc = tCrkService.findDc(map);
		Workbook workbook = Excel2007Utils.getWorkbook(ServletUtils.getExcelPath(EXCEL_CRK));
		dc(workbook, dc);
		String fileName = "出入库明细" + Excel2007Utils.SUFFIX;
		ServletUtils.download(workbook, fileName, request, response);
	}

	@SuppressWarnings("unchecked")
	private void dc(Workbook workbook, Map<String, Object> dc) {
		List<Map<String, Object>> list = (List<Map<String, Object>>) dc.get("list");
		if (list.isEmpty()) {
			return;
		}
		List<TYs> ys = (List<TYs>) dc.get("ys");
		if (ys.isEmpty()) {
			return;
		}
		ExcelPlugin plugin = new ExcelPlugin(workbook);
		Sheet sheet = workbook.getSheetAt(0);
		Row row2 = sheet.createRow(2);
		Row row3 = sheet.createRow(3);
		Row row4 = sheet.createRow(4);
		Row row5 = sheet.createRow(5);
		Row row6 = sheet.createRow(6);
		Row row7 = sheet.createRow(7);
		row2.setHeightInPoints(15f);
		row3.setHeightInPoints(15f);
		row4.setHeightInPoints(15f);
		row5.setHeightInPoints(15f);
		row6.setHeightInPoints(15f);
		row7.setHeightInPoints(15f);
		for (int i = 0; i < 4; i++) {
			row6.createCell(i).setCellStyle(plugin.STYLE_FONT10);
			row7.createCell(i).setCellStyle(plugin.STYLE_FONT10);
		}
		row6.getCell(0).setCellValue("序号");
		sheet.addMergedRegion(new CellRangeAddress(6, 7, 0, 0));
		row6.getCell(1).setCellValue("Cn.No");
		sheet.addMergedRegion(new CellRangeAddress(6, 7, 1, 1));
		row6.getCell(2).setCellValue("区位号");
		sheet.addMergedRegion(new CellRangeAddress(6, 7, 2, 2));
		row6.getCell(3).setCellValue("客户");
		sheet.addMergedRegion(new CellRangeAddress(6, 7, 3, 3));
		int col = 4;
		for (int i = 0; i < ys.size(); i++) {
			TYs tYs = ys.get(i);
			row4.createCell(col + i).setCellStyle(plugin.STYLE_FONT10_RED);
			row5.createCell(col + i).setCellStyle(plugin.STYLE_FONT10);
			row6.createCell(col + i).setCellStyle(plugin.STYLE_FONT10_RED);
			row7.createCell(col + i).setCellStyle(plugin.STYLE_FONT10);
			if (i == 0) {
				row4.getCell(col + i).setCellValue("化学成份平均值");
				row6.getCell(col + i).setCellValue("入库时分析成份");
			}
			row7.getCell(col + i).setCellValue(tYs.getYs());
		}
		sheet.addMergedRegion(new CellRangeAddress(4, 4, col, col + ys.size() - 1));
		sheet.addMergedRegion(new CellRangeAddress(6, 6, col, col + ys.size() - 1));
		col = col + ys.size();
		row3.createCell(col).setCellStyle(plugin.STYLE_FONT10_BOLD);
		row4.createCell(col).setCellStyle(plugin.STYLE_FONT10_BOLD);
		row5.createCell(col).setCellStyle(plugin.STYLE_FONT10_RED);
		row6.createCell(col).setCellStyle(plugin.STYLE_FONT10_BOLD);
		row7.createCell(col).setCellStyle(plugin.STYLE_FONT10);
		row3.getCell(col).setCellValue("上限");
		row4.getCell(col).setCellValue("下限");
		row5.getCell(col).setCellValue("出货前");
		row6.getCell(col).setCellValue("分析时间");
		++col;
		for (int i = 0; i < ys.size(); i++) {
			TYs tYs = ys.get(i);
			row2.createCell(col + i).setCellStyle(plugin.STYLE_FONT10_BOLD);
			row3.createCell(col + i).setCellStyle(plugin.STYLE_FONT10);
			row4.createCell(col + i).setCellStyle(plugin.STYLE_FONT10);
			row5.createCell(col + i).setCellStyle(plugin.STYLE_FONT10);
			row6.createCell(col + i).setCellStyle(plugin.STYLE_FONT10_RED);
			row7.createCell(col + i).setCellStyle(plugin.STYLE_FONT10);
			if (i == 0) {
				row2.getCell(col + i).setCellValue("社内规格");
				row6.getCell(col + i).setCellValue("出货前分析成份");
			}
			row3.getCell(col + i).setCellValue(tYs.getSx());
			row4.getCell(col + i).setCellValue(tYs.getXx());
			row7.getCell(col + i).setCellValue(tYs.getYs());
		}
		sheet.addMergedRegion(new CellRangeAddress(2, 2, col, col + ys.size() - 1));
		sheet.addMergedRegion(new CellRangeAddress(6, 6, col, col + ys.size() - 1));
		col = col + ys.size();
		for (int i = col; i < col + 4; i++) {
			row6.createCell(i).setCellStyle(plugin.STYLE_FONT10);
			row7.createCell(i).setCellStyle(plugin.STYLE_FONT10);
		}
		row6.getCell(col).setCellValue("入库日期");
		sheet.addMergedRegion(new CellRangeAddress(6, 7, col, col));
		row6.getCell(++col).setCellValue(new XSSFRichTextString("入库重量\n(kg)"));
		sheet.addMergedRegion(new CellRangeAddress(6, 7, col, col));
		row6.getCell(++col).setCellValue(new XSSFRichTextString("在库重量\n(kg)"));
		sheet.addMergedRegion(new CellRangeAddress(6, 7, col, col));
		row6.getCell(++col).setCellValue("捆数");
		sheet.addMergedRegion(new CellRangeAddress(6, 7, col, col));
		++col;
		for (int i = 0; i < LEN_CKJL * 2; i++) {
			row6.createCell(col + i).setCellStyle(plugin.STYLE_FONT10);
			row7.createCell(col + i).setCellStyle(plugin.STYLE_FONT10);
			if (i == 0) {
				row6.getCell(col + i).setCellValue("出库记录");
			}
			if (i % 2 == 0) {
				row7.getCell(col + i).setCellValue("出库重量");
			} else {
				row7.getCell(col + i).setCellValue("出库日期");
			}
		}
		sheet.addMergedRegion(new CellRangeAddress(6, 6, col, col + LEN_CKJL * 2 - 1));
		col = col + LEN_CKJL * 2;
		for (int i = col; i < col + 3; i++) {
			row6.createCell(i).setCellStyle(plugin.STYLE_FONT10);
			row7.createCell(i).setCellStyle(plugin.STYLE_FONT10);
		}
		row6.getCell(col).setCellValue("K值");
		sheet.addMergedRegion(new CellRangeAddress(6, 7, col, col));
		row6.getCell(++col).setCellValue("检查者");
		sheet.addMergedRegion(new CellRangeAddress(6, 7, col, col));
		row6.getCell(++col).setCellValue("确认者");
		sheet.addMergedRegion(new CellRangeAddress(6, 7, col, col));
		++col;
		for (int i = 0; i < ys.size(); i++) {
			TYs tYs = ys.get(i);
			row2.createCell(col + i).setCellStyle(plugin.STYLE_FONT10_RED);
			row4.createCell(col + i).setCellStyle(plugin.STYLE_FONT10_BOLD);
			row5.createCell(col + i).setCellStyle(plugin.STYLE_FONT10);
			row6.createCell(col + i).setCellStyle(plugin.STYLE_FONT10_BOLD);
			row7.createCell(col + i).setCellStyle(plugin.STYLE_FONT10);
			if (i == 0) {
				row2.getCell(col + i).setCellValue("偏差显示红色字体时说明超出基准，立即上报！");
				row4.getCell(col + i).setCellValue("偏差基准");
				row6.getCell(col + i).setCellValue("出货前分析成份");
			}
			row5.getCell(col + i).setCellValue(tYs.getPc());
			row7.getCell(col + i).setCellValue(tYs.getYs());
		}
		sheet.addMergedRegion(new CellRangeAddress(2, 2, col, col + ys.size() - 1));
		sheet.addMergedRegion(new CellRangeAddress(4, 4, col, col + ys.size() - 1));
		sheet.addMergedRegion(new CellRangeAddress(6, 6, col, col + ys.size() - 1));
		for (int i = 0; i < list.size(); i++) {
			Map<String, Object> map = list.get(i);
			Row row = sheet.createRow(8 + i);
			for (int j = 0; j < 4; j++) {
				row.createCell(j).setCellStyle(plugin.STYLE_FONT10);
			}
			row.getCell(0).setCellValue(i + 1);
			row.getCell(1).setCellValue((String) map.get("cpbh"));
			row.getCell(2).setCellValue(String.join(",", (Set<String>) map.get("kqmc")));
			row.getCell(3).setCellValue((String) map.get("kh"));
			col = 4;
			for (int j = 0; j < ys.size(); j++) {
				row.createCell(col + j).setCellStyle(plugin.STYLE_FONT10);
			}
			List<Map<String, Object>> fxmx = (List<Map<String, Object>>) map.get("fxmx");
			for (int j = 0; j < fxmx.size(); j++) {
				Map<String, Object> fx = fxmx.get(j);
				if ((Boolean) fx.get("rkfxRed")) {
					row.createCell(col + j).setCellStyle(plugin.STYLE_FONT10_RED);
				}
				row.getCell(col + j).setCellValue(Double.valueOf((String) fx.get("rkfx")));
			}
			col = col + ys.size();
			row.createCell(col).setCellStyle(plugin.STYLE_FONT10);
			if (map.get("fxsj") != null) {
				row.getCell(col).setCellValue(DateFormatUtils.format((Timestamp) map.get("fxsj"), "yyyy/M/d"));
			}
			++col;
			for (int j = 0; j < ys.size(); j++) {
				row.createCell(col + j).setCellStyle(plugin.STYLE_FONT10);
			}
			for (int j = 0; j < fxmx.size(); j++) {
				Map<String, Object> fx = fxmx.get(j);
				if (fx.get("ckfxRed") != null) {
					if ((Boolean) fx.get("ckfxRed")) {
						row.createCell(col + j).setCellStyle(plugin.STYLE_FONT10_RED);
					}
					row.getCell(col + j).setCellValue(Double.valueOf((String) fx.get("ckfx")));
				}
			}
			col = col + ys.size();
			for (int j = 0; j < 4; j++) {
				row.createCell(col + j).setCellStyle(plugin.STYLE_FONT10);
			}
			row.getCell(col).setCellValue(DateFormatUtils.format((Timestamp) map.get("rkrq"), "yyyy/M/d"));
			row.getCell(++col).setCellValue((Integer) map.get("rkzl"));
			row.getCell(++col).setCellValue((Integer) map.get("zkzl"));
			row.getCell(++col).setCellValue((Integer) map.get("ks"));
			++col;
			for (int j = 0; j < LEN_CKJL * 2; j++) {
				row.createCell(col + j).setCellStyle(plugin.STYLE_FONT10);
			}
			List<Map<String, Object>> ckjl = (List<Map<String, Object>>) map.get("ckjl");
			int ii = 0;
			for (int j = 0; j < ckjl.size(); j++) {
				if (j >= LEN_CKJL) {
					break;
				}
				Map<String, Object> ck = ckjl.get(j);
				row.getCell(col + ii++).setCellValue((Integer) ck.get("zl"));
				row.getCell(col + ii++).setCellValue((String) ck.get("mx"));
			}
			col = col + LEN_CKJL * 2;
			for (int j = 0; j < 3; j++) {
				row.createCell(col + j).setCellStyle(plugin.STYLE_FONT10);
			}
			row.getCell(col).setCellValue((String) map.get("kz"));
			if (map.get("jcz") != null) {
				row.getCell(++col).setCellValue((String) map.get("jcz"));
			} else {
				++col;
			}
			if (map.get("qrz") != null) {
				row.getCell(++col).setCellValue((String) map.get("qrz"));
			} else {
				++col;
			}
			++col;
			for (int j = 0; j < ys.size(); j++) {
				row.createCell(col + j).setCellStyle(plugin.STYLE_FONT10);
			}
			for (int j = 0; j < fxmx.size(); j++) {
				Map<String, Object> fx = fxmx.get(j);
				if (fx.get("pcfxRed") != null) {
					if ((Boolean) fx.get("pcfxRed")) {
						row.createCell(col + j).setCellStyle(plugin.STYLE_FONT10_RED);
					}
					row.getCell(col + j).setCellValue(Double.valueOf((String) fx.get("pcfx")));
				}
			}
		}
		col = 4;
		for (int i = 0; i < ys.size(); i++) {
			row5.getCell(col + i).setCellType(XSSFCell.CELL_TYPE_FORMULA);
			row5.getCell(col + i).setCellFormula("AVERAGE(" + plugin.getColumn(col + i + 1) + "9" + ":" + plugin.getColumn(col + i + 1) + (8 + list.size()) + ")");
		}
		col = col + ys.size();
		++col;
		for (int i = 0; i < ys.size(); i++) {
			row5.getCell(col + i).setCellType(XSSFCell.CELL_TYPE_FORMULA);
			row5.getCell(col + i).setCellFormula("AVERAGE(" + plugin.getColumn(col + i + 1) + "9" + ":" + plugin.getColumn(col + i + 1) + (8 + list.size()) + ")");
		}
		workbook.setForceFormulaRecalculation(true);
	}
}